using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using MySql.Data.MySqlClient;
namespace LdExpress
{
    /// <summary>
    /// 
    /// </summary>
    public class Goods
    {
        public long ID = 0;
        public string Name = "";
        public string Code = "";
        public int Sort = 0;
        public string Description = "";
        public Goods()
        {
        }
        public Goods(long lID)
        {
            this.ID = lID;
        }
        public void Save(MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            if (isExisting(Code, con, trans) > 0)
            {
                GV.ErrorMessage = "Code '" + Code + "' is Existing!";
                return;
            }
            if (ID <= 0) // new object, we insert new record to database
            {
                // build SQL statement
                sqlCommand = "Insert into tbl_Goods (" +
                             "Name," +
                             "Code," +
                             "Sort," +
                             "Description) " +
                         " values(" +
                             "'" + DBModule.RefineString(Name) + "'," +
                             "'" + DBModule.RefineString(Code) + "'," +
                             Sort.ToString() + "," +
                             "'" + DBModule.RefineString(Description) + "')";
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, con, trans);
                UserObjectsLog.SaveObjectActionLog("Goods", "add", con, trans);
            }
            else // edit object, we update old record in database
            {
                // build SQL statement
                sqlCommand = "Update tbl_Goods set " +
                             "Name='" + DBModule.RefineString(Name) + "'," +
                             "Code='" + DBModule.RefineString(Code) + "'," +
                             "Sort=" + Sort.ToString() + "," +
                             "Description='" + DBModule.RefineString(Description) + "'" +
                    " where ID=" + ID.ToString();
                // run SQL statement
                DBModule.ExecuteNonQuery(sqlCommand, con, trans);
                UserObjectsLog.SaveObjectActionLog("Goods", "edit", con, trans);
            }
        }
        public void Delete(MySqlConnection con, MySqlTransaction trans)
        {
            //Buil SQL Statement
            //Run SQL Statement
            string sqlCommand = "DELETE FROM tbl_Goods WHERE ID = " + this.ID.ToString() + " limit 1";
            DBModule.ExecuteNonQuery(sqlCommand, con, trans);
            UserObjectsLog.SaveObjectActionLog("Goods", "delete", con, trans);
        }
        public void Load(MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT * FROM tbl_Goods WHERE ID = " + ID.ToString() + " limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Name"))
                    Name = dr["Name"].ToString();
                if (!dr.IsNull("Code"))
                    Code = dr["Code"].ToString();
                if (!dr.IsNull("Sort"))
                    Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("Description"))
                    Description = dr["Description"].ToString();
            }
            else
            {
            }
        }
        public void LoadByCode(string code, MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            //Build SQL Statement
            //Run SQL Statement
            sqlCommand = "SELECT * FROM tbl_Goods WHERE Code = '" + DBModule.RefineString(code) + "' limit 0,1";
            DataSet ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
            //Fill data into this object
            if (ds.Tables[0].Rows.Count > 0)
            {
                DataRow dr = ds.Tables[0].Rows[0];
                if (!dr.IsNull("ID"))
                    ID = long.Parse(dr["ID"].ToString());
                if (!dr.IsNull("Name"))
                    Name = dr["Name"].ToString();
                if (!dr.IsNull("Code"))
                    Code = dr["Code"].ToString();
                if (!dr.IsNull("Sort"))
                    Sort = int.Parse(dr["Sort"].ToString());
                if (!dr.IsNull("Description"))
                    Description = dr["Description"].ToString();
            }
            else
            {
            }
        }
        public static string GetNameByCode(string code, MySqlConnection con, MySqlTransaction trans)
        {
            string retVal = "";
            //Build SQL Statement
            //Run SQL Statement
            string sqlCommand = "SELECT Name FROM tbl_Goods WHERE Code = '" + DBModule.RefineString(code) + "' limit 0,1";
            string tmp = DBModule.ExecuteQueryForOneResult(sqlCommand, con, trans);
            if (!String.IsNullOrEmpty(tmp))
            {
                retVal = tmp;
            }
            return retVal;
        }
        public static void GetList(string Codition, string OrderBy, int NumOfItemsPerPage, int PageNo, out int NumberOfPages, out DataSet ds, MySqlConnection con, MySqlTransaction trans)
        {
            NumberOfPages = 0;
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select *,CONVERT(SUBSTRING_INDEX(tbl_Goods.Code,'-',-1),UNSIGNED INTEGER) AS numCode from tbl_Goods where 1=1 ";
            if (Codition.Length > 0)
            {
                sqlCommand += Codition;
            }

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            if (NumOfItemsPerPage > 0)
            {
                ds = DBModule.ExecuteQuery(sqlCommand, NumOfItemsPerPage, PageNo, out NumberOfPages, con, trans);
            }
            else
                ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
        }
        public static void DoSearch(string keywords, string OrderBy, out DataSet ds, MySqlConnection con, MySqlTransaction trans)
        {
            string sqlCommand = "";
            ds = null;
            // build SQL statement
            sqlCommand = "Select `ID` from tbl_Goods where 1=1 ";

            if ((OrderBy != null) && (OrderBy != ""))
                sqlCommand = sqlCommand + " Order By " + DBModule.RefineString(OrderBy);
            // run SQL statement
            ds = DBModule.ExecuteQuery(sqlCommand, con, trans);
        }
        public static long isExisting(string code, MySqlConnection con, MySqlTransaction trans)
        {
            long ID = 0;
            string sqlCommand = "Select ID from tbl_Goods where Code='" + DBModule.RefineString(code) + "' limit 0,1";
            string strID = DBModule.ExecuteQueryForOneResult(sqlCommand, con, trans);
            if (strID != null)
            {
                try
                {
                    ID = long.Parse(strID);
                }
                catch { }
            }
            return ID;
        }
    }
}
